package com.bdwm.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.bdwm.db.DataBaseConn;
import com.bdwm.model.Constants;
import com.bdwm.model.User;

/**
 * User信息的数据库操作服务
 * @author wangwenhui
 * 
 */
public class UserDBService {
	
	public static int addUser(User user) throws SQLException, ClassNotFoundException {
		int status =Constants.ERROR;
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DataBaseConn.mysqlConn();
			String insertSql = "insert into user_info (user_name,user_email,user_department,user_telephone,password)"+ " values(?,?,?,?,?)"; 
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(insertSql);
			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getUserEmail());
			pstmt.setString(3, user.getUserDept());
			pstmt.setString(4, user.getUserTel());
			pstmt.setString(5, user.getPassword());
			status = pstmt.executeUpdate();
			if(status != 0){ 
				status = Constants.SUCCESS; 
				conn.commit();
			}else{
				status = Constants.ERROR; 
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			conn.rollback();
		} finally {
			DataBaseConn.closeDB(null, pstmt, conn);
		}
		return status;
	}
	
	public static boolean findIfUserExist(String user_name) {
		boolean isExist = false;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				conn = DataBaseConn.mysqlConn();
				final String sql = "select count(1) as cnt from user_info where user_name = '" + user_name + "'";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				int cnt = 0;
				while (rs.next()) {
					cnt = rs.getInt("cnt");
				}
				if(cnt > 0) {
					isExist = true;
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DataBaseConn.closeDB(rs, ps, conn);
			}
		return isExist;
	}
	
	/**
	 * 
		 * @Description：获取用户信息
		 * @author：wangwenhui
		 * @time：2017年11月27日上午10:38:10
	 */
	public static User getUserInfo(String user_name) {
		User user = new User();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				conn = DataBaseConn.mysqlConn();
				final String sql = "select user_email,user_department,user_telephone,password from user_info where user_name = ?";
				ps = conn.prepareStatement(sql);
				ps.setString(1, user_name);
				rs = ps.executeQuery();
				while (rs.next()) {
//					user.setUserId(rs.getInt("user_id"));
					user.setUserName(user_name);
					user.setUserEmail(rs.getString("user_email"));
					user.setUserDept(rs.getString("user_department"));
					user.setUserTel(rs.getString("user_telephone"));
					user.setPassword(rs.getString("password"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DataBaseConn.closeDB(rs, ps, conn);
			}
		return user;
	}
	
	/**
	 * 
		 * @Description：登录
		 * @author：wangwenhui
		 * @time：2017年11月29日下午4:40:11
	 */
	public static void login() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				conn = DataBaseConn.mysqlConn();
				final String sql = "select user_id,user_email,user_department,user_telephone,password from user_info";
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while (rs.next()) {
					User user = new User();
					user.setUserId(rs.getInt("user_id"));
					user.setUserEmail(rs.getString("user_email"));
					user.setUserDept(rs.getString("user_department"));
					user.setUserTel(rs.getString("user_telephone"));
					user.setPassword(rs.getString("password"));
					System.out.println(user);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				DataBaseConn.closeDB(rs, ps, conn);
			}
	}
	
	
	
	public static void main(String[] args) throws ClassNotFoundException, SQLException {

		/*User user = new User();
		user.setUserName("wangwenhui");
		user.setUserEmail("wangwenhui@baiduwaimai.com");
		user.setUserDept("app_dept");
		user.setUserTel("15322221111");*/
		
		System.out.println(findIfUserExist("王文慧"));
		//System.out.println(DataBaseConn.mysqlConn());
	}
}
